package com.zhufei.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.Jsp_Topic1;
import com.zhufei.entity.Sponsor;

public class Jsp_Topic1Dao extends DBCon{
	private static String TABLE = "jsg_topic1";
	private static String TOPIC_ID = "topic_id";
	private static String TOPIC_NAME = "topic_name";
	private static String TOPIC_POSTTIME = "topic_posttime";
	private static String TOPIC_NODE_NAME = "topic_node_name";
	private static String TOPIC_POSTER_NAME = "topic_poster_name";
	/**
	 * 获取数据库中某栏目帖子总数
	 * @param name 帖子栏目的名字
	 * @return  返回数据库某栏目有多少个帖子记录
	 */
	public static int getCountOfPost(String name){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE+" where "+TOPIC_NODE_NAME+"='"+name+"'";
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				return count;
			}
			else 
				return 0;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的帖子对象
	 * @param index 从第index条开始获取（第一条下标是0）
	 * @param number 获取的数量，输入-1则获取数据库中所有帖子
	 * @param name 栏目名称
	 * @return  赞助对象的ArrayList
	 */
	public static ArrayList<Jsp_Topic1> getPosts(int index, int number, String name){
		ArrayList<Jsp_Topic1> postList = new ArrayList<Jsp_Topic1>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			if(number != -1){
				sql = "select "+TOPIC_ID+","+TOPIC_NAME+","+TOPIC_POSTTIME+","+TOPIC_POSTER_NAME+" from "+ TABLE +" where "+TOPIC_NODE_NAME+"='"+name+"' order by " + TOPIC_POSTTIME+"  limit "+index+","+number;
			}else{
				sql = "select "+TOPIC_ID+","+TOPIC_NAME+","+TOPIC_POSTTIME+","+TOPIC_POSTER_NAME+" from "+ TABLE +" where "+TOPIC_NODE_NAME+"='"+name+"' order by " + TOPIC_POSTTIME+" ";
			}
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				Jsp_Topic1 post = new Jsp_Topic1();
				post.setTopic_id(rs.getString(TOPIC_ID));
				post.setTopic_name(rs.getString(TOPIC_NAME));
				post.setTopic_posttime(rs.getString(TOPIC_POSTTIME));
				post.setTopic_node_name(name);
				post.setUsername(rs.getString(TOPIC_POSTER_NAME));
				post.setTopic_id("topic_"+post.getTopic_id()+"_1.html");
				postList.add(post);
			}
			rs.close();
			return postList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
}
